/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 80400
 Source Host           : localhost:3306
 Source Schema         : demo

 Target Server Type    : MySQL
 Target Server Version : 80400
 File Encoding         : 65001

 Date: 08/12/2024 15:58:38
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for checi
-- ----------------------------
DROP TABLE IF EXISTS `checi`;
CREATE TABLE `checi`  (
  `id` int NOT NULL COMMENT '车次id',
  `vehicleId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '车辆id',
  `routeId` int NULL DEFAULT NULL COMMENT '路线id',
  `startTime` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '发车时间',
  `endTime` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '到站时间',
  `lastSeats` int NULL DEFAULT NULL COMMENT '剩余座位数',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `vehicleId`(`vehicleId` ASC) USING BTREE,
  INDEX `routeId`(`routeId` ASC) USING BTREE,
  CONSTRAINT `checi_ibfk_1` FOREIGN KEY (`vehicleId`) REFERENCES `vehicle` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `checi_ibfk_2` FOREIGN KEY (`routeId`) REFERENCES `route` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of checi
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for drive_table
-- ----------------------------
DROP TABLE IF EXISTS `drive_table`;
CREATE TABLE `drive_table`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `driverId` int NOT NULL COMMENT '司机id',
  `vehicleId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '车辆id',
  `createTime` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建时间',
  `endTime` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '结束时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `driverId`(`driverId` ASC) USING BTREE,
  INDEX `vehicleId`(`vehicleId` ASC) USING BTREE,
  CONSTRAINT `drive_table_ibfk_1` FOREIGN KEY (`driverId`) REFERENCES `driver` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `drive_table_ibfk_2` FOREIGN KEY (`vehicleId`) REFERENCES `vehicle` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '司机驾驶表';

-- ----------------------------
-- Records of drive_table
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for driver
-- ----------------------------
DROP TABLE IF EXISTS `driver`;
CREATE TABLE `driver`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '司机编号',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '司机姓名',
  `sex` tinyint NULL DEFAULT NULL COMMENT '司机性别 0女 1男',
  `birthday` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '出生日期',
  `license` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '所持驾照A1,A2,A3,B1',
  `phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系电话',
  `status` tinyint NULL DEFAULT NULL COMMENT '状态1在任 0停职',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '司机表';

-- ----------------------------
-- Records of driver
-- ----------------------------
BEGIN;
INSERT INTO `driver` (`id`, `name`, `sex`, `birthday`, `license`, `phone`, `status`) VALUES (7, '王伟', 1, '2000-07-02T16:00:00.000Z', 'A1', '12345678910', 1), (8, '莉萨', 0, '2001-05-08T16:00:00.000Z', 'A3', '18773567301', 1);
COMMIT;

-- ----------------------------
-- Table structure for order_table
-- ----------------------------
DROP TABLE IF EXISTS `order_table`;
CREATE TABLE `order_table`  (
  `id` int NOT NULL COMMENT '订单id',
  `ticketId` int NULL DEFAULT NULL COMMENT '票价id',
  `userId` int NULL DEFAULT NULL COMMENT '用户id',
  `createTime` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建时间',
  `status` tinyint NULL DEFAULT NULL COMMENT '状态 0退票 1生效',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `userId`(`userId` ASC) USING BTREE,
  INDEX `ticketId`(`ticketId` ASC) USING BTREE,
  CONSTRAINT `order_table_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `order_table_ibfk_3` FOREIGN KEY (`ticketId`) REFERENCES `ticket` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of order_table
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for route
-- ----------------------------
DROP TABLE IF EXISTS `route`;
CREATE TABLE `route`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '路线编号',
  `departure` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '出发地',
  `destination` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '目的地',
  `stops` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '途经站点，以,分割',
  `count` int NULL DEFAULT 0 COMMENT '累计乘坐次数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '路线表';

-- ----------------------------
-- Records of route
-- ----------------------------
BEGIN;
INSERT INTO `route` (`id`, `departure`, `destination`, `stops`, `count`) VALUES (12, '起点1', '终点10', '中途点1,中途点3', 0), (13, '起点2', '终点3', '中点4,中点2', 0);
COMMIT;

-- ----------------------------
-- Table structure for ticket
-- ----------------------------
DROP TABLE IF EXISTS `ticket`;
CREATE TABLE `ticket`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '票价id',
  `checiId` int NULL DEFAULT NULL COMMENT '车次id',
  `stop` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '停靠站点名',
  `stopTime` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '停靠时间点',
  `price` double NULL DEFAULT NULL COMMENT '票价',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `checiId`(`checiId` ASC) USING BTREE,
  CONSTRAINT `ticket_ibfk_1` FOREIGN KEY (`checiId`) REFERENCES `checi` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '票价表';

-- ----------------------------
-- Records of ticket
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户名',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '密码',
  `sex` tinyint NULL DEFAULT NULL COMMENT '性别 1男 0女',
  `birthday` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '出生日期',
  `phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '电话',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `nameIdx`(`username` ASC) USING BTREE COMMENT '用户名必须唯一'
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` (`id`, `username`, `password`, `sex`, `birthday`, `phone`) VALUES (1, '张三', '12345678', 1, NULL, NULL);
COMMIT;

-- ----------------------------
-- Table structure for vehicle
-- ----------------------------
DROP TABLE IF EXISTS `vehicle`;
CREATE TABLE `vehicle`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '车牌号',
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '车辆类型 小型,中型,大型',
  `seats` int NOT NULL COMMENT '座位数',
  `createTime` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建时间',
  `status` tinyint NULL DEFAULT 1 COMMENT '状态 0停用 1正在使用',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '车辆表';

-- ----------------------------
-- Records of vehicle
-- ----------------------------
BEGIN;
INSERT INTO `vehicle` (`id`, `type`, `seats`, `createTime`, `status`) VALUES ('ABAB1243', '中型', 50, '2024-11-29', 1), ('GHYU6829', '中型', 50, '2024-11-29', 1), ('wevr3476', '中型', 30, '2024-12-06', 1);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
